﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// Summary description for _Company_bank
/// </summary>
public class _Company_bank
{
	public _Company_bank()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public int _id_company_bank { get; set; }
    public int _id_company { get; set; }
    public int _id_bank { get; set; }
    public string _bank_name { get; set; }
    public string _bank_swift { get; set; }
    public int _id_account_number { get; set; }
    public string _account { get; set; }

    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    private static _Company_bank Fill_Company_bank(SqlDataReader dr)
    {
        _Company_bank Company_bank = new _Company_bank();

        Company_bank._id_company_bank = (int)dr[0];
        Company_bank._id_company = (int)dr[1];

        Company_bank._id_bank = (int)dr[2];
        Company_bank._bank_name = _Bank.Select(dr[2].ToString())._bank_name;
        Company_bank._bank_swift = _Bank.Select(dr[2].ToString())._bank_swift;

        Company_bank._id_account_number = _Account.Select(dr[0].ToString())._id_account_number;
        Company_bank._account = _Account.Select(dr[0].ToString())._account_number;

        return Company_bank;
    }

    public static List<_Company_bank> Select_Company_bank(string company_id)
    {
        if (conn.State != ConnectionState.Closed)
        {
            conn.Close();
            List<_Company_bank> company_bank = new List<_Company_bank>();
            SqlCommand SelCmd = new SqlCommand("SELECT * FROM company_bank WHERE id_company=@company_id AND status=@statuss", conn);
            SelCmd.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Convert.ToInt32(company_id);
            SelCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = "Active";
            conn.Open();
            SqlDataReader dr = SelCmd.ExecuteReader();
            while (dr.Read())
            {
                company_bank.Add(Fill_Company_bank(dr));
            }
            dr.Close();
            conn.Close();
            return company_bank;
        }
        else if (conn.State == ConnectionState.Closed)
        {
            List<_Company_bank> company_bank = new List<_Company_bank>();
            SqlCommand SelCmd = new SqlCommand("SELECT * FROM company_bank WHERE id_company=@company_id AND status=@statuss", conn);
            SelCmd.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Convert.ToInt32(company_id);
            SelCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = "Active";
            conn.Open();
            SqlDataReader dr = SelCmd.ExecuteReader();
            while (dr.Read())
            {
                company_bank.Add(Fill_Company_bank(dr));
            }
            dr.Close();
            conn.Close();
            return company_bank;
        }
        return null;
    }

    //Bankas pievienošana iekš tabulas COMPANY_BANK
    public static _Company_bank Insert(string id_company, string id_bank, string account_number)
    {
        _Company_bank bank = new _Company_bank();
        SqlCommand AddCmd_company_bank = new SqlCommand("INSERT INTO company_bank (id_company, id_bank, status) " + "  VALUES (@id_company, @id_bank, @statuss);", conn);
        AddCmd_company_bank.Parameters.Add(new SqlParameter("@id_company", System.Data.SqlDbType.Int)).Value = id_company;
        AddCmd_company_bank.Parameters.Add(new SqlParameter("@id_bank", System.Data.SqlDbType.Int)).Value = id_bank;
        AddCmd_company_bank.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = "Active";
        conn.Open();
        AddCmd_company_bank.ExecuteNonQuery();
        conn.Close();

        SqlCommand SelMax_company_bankID = new SqlCommand("SELECT MAX(id) FROM company_bank", conn);
        conn.Open();
        string company_bank_id = Convert.ToString(SelMax_company_bankID.ExecuteScalar());
        conn.Close();

        SqlCommand AddCmd_account = new SqlCommand("INSERT INTO account_number (id_company_bank, account_number) " + "  VALUES (@id_company_bank, @account_number);", conn);
        AddCmd_account.Parameters.Add(new SqlParameter("@id_company_bank", System.Data.SqlDbType.Int)).Value = company_bank_id;
        AddCmd_account.Parameters.Add(new SqlParameter("@account_number", System.Data.SqlDbType.NVarChar)).Value = account_number;
        conn.Open();
        AddCmd_account.ExecuteNonQuery();
        conn.Close();
        return bank;
    }

    //Bankas/konta labošana MyCompanies.aspx POPUP
    public static _Company_bank Update(string company_bank_id, string company_id, string bank_id, string account_number_id, string account_number)
    {
        _Company_bank bank = new _Company_bank();
        SqlCommand UpCmd = new SqlCommand("UPDATE company_bank SET id_company=@company_id, id_bank=@bank_id WHERE ID=@ID", conn);
        UpCmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = company_bank_id;
        UpCmd.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.NVarChar)).Value = company_id;
        UpCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.NVarChar)).Value = bank_id;
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();

        SqlCommand UpCmd2 = new SqlCommand("UPDATE account_number SET id_company_bank=@company_bank_id, account_number=@account_number WHERE ID_account_number=@account_number_id", conn);
        UpCmd2.Parameters.Add(new SqlParameter("@account_number_id", System.Data.SqlDbType.Int)).Value = account_number_id;
        UpCmd2.Parameters.Add(new SqlParameter("@account_number", System.Data.SqlDbType.NVarChar)).Value = account_number;
        UpCmd2.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.NVarChar)).Value = company_bank_id;
        conn.Open();
        UpCmd2.ExecuteNonQuery();
        conn.Close();
        return bank;
    }

    //Atlasa Bankas un kontus priekš ViewInvoice.aspx un MyCompanies.aspx Popup bankas/konta labošanai
    public static _Company_bank Select(string company_bank_id)
    {
        _Company_bank bank = new _Company_bank();
        SqlCommand SelCmd = new SqlCommand("SELECT * FROM company_bank WHERE id=@company_bank_id AND status=@statuss", conn);
        SelCmd.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = company_bank_id;
        SelCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = "Active";
        conn.Open();
        SqlDataReader dr = SelCmd.ExecuteReader();
        while (dr.Read())
        {
            bank._id_company_bank = (int)dr[0];
            bank._id_company=(int)dr[1];
            bank._id_bank = (int)dr[2];
            bank._bank_name = _Bank.Select(dr[2].ToString())._bank_name.ToString();
            bank._id_account_number = _Account.Select(dr[0].ToString())._id_account_number;
            bank._account = _Account.Select(dr[0].ToString())._account_number.ToString();
        }
        dr.Close();
        conn.Close();
        return bank;
    }
    
    //Bankas/Konta statusa nomaiņa uz Deleted
    public static _Company_bank Delete(string company_bank_id)
    {
        _Company_bank bank = new _Company_bank();
        SqlCommand UpCmd = new SqlCommand("UPDATE company_bank SET status=@statuss WHERE id=@company_bank_id", conn);
        UpCmd.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = company_bank_id;
        UpCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = "Deleted";
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();
        return bank;
    }
}